package com.jiezuo.flowable.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.jiezuo.auth.entity.User;
import com.jiezuo.flowable.entity.MyFormInformation;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.flowable.ui.modeler.domain.Model;

import java.util.List;
import java.util.Map;

/**
 *@Description
 *@Author 庞留杰
 *@Date2023/8/4 9:04
 *@Version V1.0
 **/
public interface CommonMapper extends BaseMapper<Map<String, Object>> {

    @Select(" SELECT DISTINCT " +
            "    RES.BUSINESS_KEY_ " +
            " FROM " +
            " ACT_HI_PROCINST RES " +
            " LEFT OUTER JOIN ACT_RE_PROCDEF DEF ON RES.PROC_DEF_ID_ = DEF.ID_ " +
            " WHERE " +
            "    RES.START_USER_ID_=#{userId} AND DEF.KEY_=#{processDefinitionKey} "
    )
    List<String> apply(@Param("userId") String userId, @Param("processDefinitionKey") String processDefinitionKey);

    List<Map<String, Object>> todo(@Param("userId") String userId,
                                   @Param("groupId_1") String[] groupId_1,
                                   @Param("groupId_2") String[] groupId_2,
                                   @Param("processDefinitionKey") String processDefinitionKey);

    @Select(" SELECT DISTINCT " +
            "  AHP.BUSINESS_KEY_ " +
            "FROM " +
            " ACT_HI_TASKINST RES " +
            " LEFT OUTER JOIN ACT_HI_VARINST VAR ON RES.PROC_INST_ID_ = VAR.EXECUTION_ID_  " +
            " LEFT  JOIN ACT_HI_PROCINST AHP ON RES.PROC_INST_ID_ = AHP.PROC_INST_ID_  " +
            "WHERE " +
            " RES.ASSIGNEE_ = #{userId} AND AHP.PROC_DEF_ID_ like concat(#{processDefinitionKey},':%') " +
            " AND RES.END_TIME_ IS NOT NULL  ")
    List<String> done(@Param("userId") String userId, @Param("processDefinitionKey") String processDefinitionKey);

    List<User> findTodoPersonsByProcessInstanceId(@Param("processInstanceId") String processInstanceId);

    @Select("SELECT id, user_code, user_name, real_name, status FROM sys_user_view WHERE is_delete=0")
    List<Map<String, String>> findAllUser();

    @Select("SELECT concat(#{key},id) AS id, code, name, status FROM sys_depart_view WHERE is_delete=0")
    List<Map<String, String>> findAllDepart(@Param("key") String key);

    @Select("SELECT concat(#{key},id) AS id, code, name, status FROM sys_position_view WHERE is_delete=0")
    List<Map<String, String>> findAllPosition(@Param("key") String key);

    @Select("SELECT concat(#{key},id) AS id, code, name, status FROM sys_position_group_view WHERE is_delete=0")
    List<Map<String, String>> findAllPositionGroup(@Param("key") String key);

    @Select("SELECT concat(#{key},id) AS id, code, name, status, level FROM sys_position_level_view WHERE is_delete=0")
    List<Map<String, String>> findAllPositionLevel(@Param("key") String key);

    @Select(" SELECT sdu.depart_id FROM sys_depart_user_view sdu WHERE sdu.user_id=#{user_id} ")
    List<String> findDepartIdsByUserId(@Param("user_id") String user_id);

    @Select("<script>" +
            " SELECT * FROM sys_position_group_view spg WHERE spg.is_delete=0" +
            " <if test='positions != null and positions.length > 0'> " +
            "  AND " +
            " <foreach collection='positions' index='index' item='id' open='(' separator=' OR ' close=')'>FIND_IN_SET(#{id},spg.position)</foreach> " +
            " </if> " +
            "</script>")
    List<String> findPositionGroupIdsByPositions(@Param("positions") String[] positions);

    @Select("<script>" +
            " SELECT DISTINCT sp.level_id FROM sys_position_view sp WHERE sp.is_delete=0 " +
            " <if test='positions != null and positions.length > 0'> " +
            "  AND sp.id IN" +
            " <foreach collection='positions' index='index' item='id' open='(' separator=',' close=')'>#{id}</foreach> " +
            " </if> " +
            "</script>")
    List<String> findPositionLevelIdsByPositions(@Param("positions") String[] positions);

    @Select("SELECT su.main_dept,sd.ancestors AS dept_ancestors FROM sys_user_view su " +
            "LEFT JOIN sys_depart_view sd ON su.main_dept=sd.id AND sd.is_delete=0 " +
            "WHERE su.id=#{userId}")
    Map<String, String> findMainDeptByUserId(@Param("userId") String userId);

    @Select(" SELECT DISTINCT " +
            "    RES.BUSINESS_KEY_,RES.PROC_INST_ID_ " +
            " FROM " +
            " ACT_HI_PROCINST RES " +
            " LEFT OUTER JOIN ACT_RE_PROCDEF DEF ON RES.PROC_DEF_ID_ = DEF.ID_ " +
            " WHERE " +
            "    RES.START_USER_ID_=#{userId} AND DEF.KEY_=#{processDefinitionKey} "
    )
    List<Map<String, Object>> apply2(@Param("userId") String userId, @Param("processDefinitionKey") String processDefinitionKey);

    @Select(" SELECT DISTINCT " +
            "  AHP.BUSINESS_KEY_ ,RES.PROC_INST_ID_ " +
            "FROM " +
            " ACT_HI_TASKINST RES " +
            " LEFT OUTER JOIN ACT_HI_VARINST VAR ON RES.PROC_INST_ID_ = VAR.EXECUTION_ID_  " +
            " LEFT  JOIN ACT_HI_PROCINST AHP ON RES.PROC_INST_ID_ = AHP.PROC_INST_ID_  " +
            "WHERE " +
            " RES.ASSIGNEE_ = #{userId} AND AHP.PROC_DEF_ID_ like concat(#{processDefinitionKey},':%') " +
            " AND RES.END_TIME_ IS NOT NULL  ")
    List<Map<String, Object>> done2(@Param("userId") String userId, @Param("processDefinitionKey") String processDefinitionKey);

    // 只查：流程引擎类型：0
    @Select("SELECT * FROM act_de_model WHERE model_key=#{model_key} AND model_type='0'")
    Model findModelByKey(@Param("model_key") String model_key);

//    @Select("SELECT GROUP_CONCAT(su.id) FROM sys_user su WHERE FIND_IN_SET( " +
//            "(SELECT sp.id FROM sys_position sp WHERE sp.name='合同核算' AND sp.is_delete=0) " +
//            ",su.positions) AND su.is_delete=0")
//    String findContractAccountingPersonnel();
//
//    @Select("SELECT GROUP_CONCAT(su.id) FROM sys_user su WHERE " +
//            "FIND_IN_SET((SELECT sp.id FROM sys_position sp WHERE sp.name='院长' AND sp.is_delete=0),su.positions) AND su.is_delete=0 " +
//            "AND " +
//            "FIND_IN_SET(#{deptId},( SELECT sd.ancestors FROM sys_depart sd WHERE sd.id=su.main_dept)) AND su.is_delete=0")
//    String findDeanByDeptId(@Param("deptId") String deptId);

    List<Map<String, String>> findGroupIdsByProcessInstanceId(@Param("processInstanceId") String processInstanceId);

    List<Map<String, String>> findInfosByProcessInstanceId(@Param("processInstanceId") String processInstanceId);

    List<Map<String, String>> findInfos(@Param("entity") Map<String, String> entity);

    List<Map<String, Object>> message(@Param("userId") String userId,
                                      @Param("groupId_1") String[] groupId_1,
                                      @Param("groupId_2") String[] groupId_2,
                                      @Param("excludeId") List<String> excludeId);

    @Select("SELECT  DISTINCT  " +
            "  smrd.activity_id  " +
            "FROM sys_message_router_details smrd " +
            "WHERE smrd.is_delete=0")
    List<String> findExcludeId();


//    @Select("SELECT su1.id FROM sys_user su1 WHERE su1.is_delete=0 AND FIND_IN_SET((SELECT sp.id FROM sys_position sp WHERE sp.is_delete=0 AND sp.id='199'),su1.positions) " +
//            "AND FIND_IN_SET(su1.main_dept, (SELECT sd.ancestors FROM sys_user su LEFT JOIN sys_depart sd ON sd.id=su.main_dept WHERE su.id=#{startUserId})) " +
//            "UNION " +
//            "SELECT su.id FROM sys_user su WHERE su.is_delete=0 AND FIND_IN_SET((SELECT sp.id FROM sys_position sp WHERE sp.is_delete=0 AND sp.id=201),su.positions)")
//    List<String> findOfficerByStartUserId(@Param("startUserId") String startUserId);


//    @Select("SELECT su1.id FROM sys_user su1 WHERE su1.is_delete=0 AND FIND_IN_SET((SELECT sp.id FROM sys_position sp WHERE sp.is_delete=0 AND sp.id='199'),su1.positions) " +
//            "AND FIND_IN_SET(su1.main_dept, (SELECT sd.ancestors FROM sys_user su LEFT JOIN sys_depart sd ON sd.id=su.main_dept WHERE su.id=#{userId}))")
//    List<String> findBussinessByUserId(@Param("userId") String userId);

//    @Update("UPDATE act_de_model SET tenant_id=#{tenantId} WHERE id=#{modelId}")
//    void updateTenantIdByModelId(@Param("modelId") String modelId, @Param("tenantId") String tenantId);
//
    List<String> findUsersByPositions(@Param("positions") List<String> positions);

    List<String> findUsersByDepts(@Param("depts") List<String> depts);

    @Select(" ${sql} ")
    List<String> findUsersBySql(@Param("sql") String sql);

    @Select("SELECT * FROM flw_my_form_information WHERE is_delete=0 AND process_instance_id=#{processInstanceId} LIMIT 1")
    MyFormInformation findFormByProcessInstanceId(@Param("processInstanceId") String processInstanceId);

//    @Select("SELECT arp.NAME_,arp.KEY_ FROM " +
//            "act_hi_procinst ahp " +
//            "LEFT JOIN act_re_procdef arp ON ahp.PROC_DEF_ID_=arp.ID_ " +
//            "WHERE ahp.PROC_INST_ID_=#{processInstanceId} LIMIT 1")
//    Map<String, Object> findModelByProcessInstanceId(@Param("processInstanceId") String processInstanceId);
//
//    @Select("SELECT sur.user_id FROM sys_role sr " +
//            "LEFT JOIN sys_user_role sur ON sur.role_id=sr.id " +
//            "LEFT JOIN sys_user su ON sur.user_id=su.id " +
//            "WHERE sr.code=#{roleCode} AND su.is_delete=0 ")
//    List<String> findUserIdByRoleCode(@Param("roleCode") String roleCode);


    //判断申请人是否事业部
    @Select("SELECT " +
            "IF((SELECT count(1) FROM sys_depart_view sd1 WHERE sd1.is_delete=0 AND sd1.section=1 AND FIND_IN_SET(sd1.id,sd.ancestors))>0,1,0) AS  centre," +
            "IF((SELECT count(1) FROM sys_depart_view sd1 WHERE sd1.is_delete=0 AND sd1.section=2 AND FIND_IN_SET(sd1.id,sd.ancestors))>0,1,0) AS  division " +
            "FROM sys_user_view su LEFT JOIN sys_depart_view sd ON sd.id=su.main_dept WHERE su.id=#{startUserId}")
    Map<String, Object> findSectionByStartUserId(@Param("startUserId") String startUserId);


    @Select("SELECT su1.id FROM sys_user_view su1 WHERE su1.is_delete=0 AND FIND_IN_SET((SELECT sp.id FROM sys_position_view sp WHERE sp.is_delete=0 AND sp.id='199'),su1.positions) " +
            "AND FIND_IN_SET(su1.main_dept, (SELECT sd.ancestors FROM sys_user_view su LEFT JOIN sys_depart_view sd ON sd.id=su.main_dept WHERE su.id=#{userId}))")
    List<String> findBussinessByUserId(@Param("userId") String userId);

    // 通过用户查询院长（岗位表中“事业部院长”）
    @Select("SELECT " +
            " su2.id,su2.real_name " +
            "FROM sys_user_view su  " +
            "LEFT JOIN sys_depart_view sd ON sd.id=su.main_dept AND sd.is_delete=0 " +
            "LEFT JOIN sys_user_view su2 ON su2.is_delete=0 AND FIND_IN_SET(su2.main_dept,sd.ancestors) AND FIND_IN_SET('223',su2.positions) " +
            "WHERE su.is_delete=0 AND su.id=#{userId} ")
    List<String> findBusinessDepartDeanByUserId(@Param("userId") String userId);

    // 通过用户查询生产院长（岗位表中“生产院长”）
    @Select("SELECT " +
            " su2.id,su2.real_name " +
            "FROM sys_user_view su  " +
            "LEFT JOIN sys_depart_view sd ON sd.id=su.main_dept AND sd.is_delete=0 " +
            "LEFT JOIN sys_user_view su2 ON su2.is_delete=0 AND FIND_IN_SET(su2.main_dept,sd.ancestors) AND FIND_IN_SET('200',su2.positions) " +
            "WHERE su.is_delete=0 AND su.id=#{userId} ")
    List<String> findBusinessProductionDepartDeanByUserId(@Param("userId") String userId);

    // 通过用户查询产业院长
    @Select("SELECT " +
            " su2.id,su2.real_name " +
            "FROM sys_user_view su  " +
            "LEFT JOIN sys_depart_view sd ON sd.id=su.main_dept AND sd.is_delete=0 " +
            "LEFT JOIN sys_user_view su2 ON su2.is_delete=0 AND FIND_IN_SET('232',su2.positions) " +
            "LEFT JOIN sys_depart_view sd2 ON sd2.id = su2.main_dept AND sd2.is_delete=0 " +
            "WHERE su.is_delete=0 AND sd.affiliation_enterprise = sd2.affiliation_enterprise AND su.id=#{userId} ")
    List<String> findBusinessIndustryDeanByUserId(@Param("userId") String userId);

    // 查询当前用户的部门信息
    @Select("SELECT su.real_name,su.main_dept,sd.name,sd.level,sd.ancestors,sd.main_leader FROM sys_user_view su " +
            "LEFT JOIN sys_depart_view sd ON sd.id=su.main_dept AND sd.is_delete=0 " +
            "WHERE su.id=#{userId} AND su.is_delete=0 ")
    Map<String,Object> findDepartLevelInfoByUserId(@Param("userId") String userId);

    // 通过祖级部门查询一级部门信息
    @Select("SELECT sd.name,sd.level,sd.ancestors,sd.main_leader FROM sys_depart_view sd WHERE FIND_IN_SET(sd.id,#{ancestors}) AND sd.is_delete=0 AND sd.level=1")
    List<Map<String,Object>> findDepartLevelInfoByAncestors(@Param("ancestors") String ancestors);



    @Select(" SELECT   " +
            "  su.id,  " +
            "  su.main_dept,  " +
            "  sd.ancestors,  " +
            "  sd.main_leader,  " +
            "  sd.level,  " +
            "  IF((SELECT count(1) FROM sys_depart_view sd2 WHERE sd2.is_delete=0 AND sd2.section=1 AND FIND_IN_SET(sd2.id,sd.ancestors))>0,1,0) AS is_centre,  " +
            "  IF((SELECT count(1) FROM sys_depart_view sd2 WHERE sd2.is_delete=0 AND sd2.section=2 AND FIND_IN_SET(sd2.id,sd.ancestors))>0,1,0) AS is_test  " +
            "FROM sys_user_view su  " +
            "LEFT JOIN sys_depart_view sd ON su.main_dept=sd.id AND sd.is_delete=0 " +
            "WHERE su.id=#{startUserId} ")
    Map<String, Object> findInfoByStartUserId(@Param("startUserId") String startUserId);

    // 查询一级部门
    @Select("<script>" +
            " SELECT    " +
            "   DISTINCT   " +
           // "   (SELECT sd2.id FROM sys_depart sd2 WHERE sd2.is_delete=0 AND sd2.level=1 AND FIND_IN_SET(sd2.id,sd.ancestors) LIMIT 1) AS id,   " +
            "   (SELECT sd2.main_leader FROM sys_depart_view sd2 WHERE sd2.is_delete=0 AND sd2.level=1 AND FIND_IN_SET(sd2.id,sd.ancestors) LIMIT 1) AS main_leader   " +
            " FROM sys_depart_view sd WHERE sd.is_delete=0 " +
            " <if test='ids != null and ids.size() > 0'> " +
            "   AND sd.id IN" +
            "   <foreach collection='ids' index='index' item='id' open='(' separator=',' close=')'>#{id}</foreach> " +
            " </if> " +
            "</script>")
    List<String> findFirstLevelDepartByIds(@Param("ids") List<String> ids);

}
